Conditional Results – the IF Function
In This Topic...
The If function is used to define the value of a calculated field based on other data, such as system information or the content of other fields in the workflow.
The condition of the function can either be a pair of values and a comparison operator, or a trigger defined in the system. The function returns one value if the condition evaluates as true, or a different value if the condition evaluates as false.
There are two versions of the If statement. The If/Then version, which is formatted as follows.
If Condition then TrueValue else FalseValue endif
Then there is the $If() version, which is formatted as follows.
$If(TriggerCode, TrueValue, FalseValue)
The If/Then version is more powerful and flexible, so it is the recommended version. The $If() version will most commonly be encountered when reviewing older calculated fields.
The If/Then Conditional Structure
The Condition is any conditional expression that evaluates to boolean values (True or False). This can be a simple field containing boolean values, a function that returns a boolean value, a comparison between two values, or even multiple conditions that resolve to a single boolean result. The Trigger() function can also be used to evaluate a trigger with additional conditions.
See the section on Building Conditional Expressions for information on operators, negative conditions, and combined conditions.
The $If() Function
The TriggerCode identifies a trigger, which is created in the system and contains all necessary conditions to evaluate as true or false. For details about configuring triggers, see
Notes:
The Code for the trigger must be used. The code is based on the name of the trigger, but may not match exactly.
If you need a conditional structure that makes use of an existing trigger configuration, the trigger can be referenced in the If/Then version using the Trigger() function.
Defining the Results
The TrueValue and FalseValue data can include placeholders, formulas, or even other functions (including other If functions). When using the If/Then structure, the else can be omitted which produces no result (null) if the function evaluates as false.
When embedding additional If functions within the true or false calculations, each If function must have its own endif parameter. As an alternative, the elseif parameter can be used in place of the else parameter. This allows multiple conditions to be chained together with only one endif for each standalone If function.
Examples:
The function could return a monthly value as an annual value, or leave it as one month, depending on the conditions.
$If(TriggerCode,[[FieldA]]*12,[[FieldA]])
-or-
If Value1 operator Value2 then [[FieldA]]*12 else [[FieldA]] endif
The function could return the user's name (concatenated from two fields with a space to divide them) or their spouse's name (also concatenated).
String:$If(TriggerCode,[[UserFirstName]] [[UserLastName]],[[SpouseFirstName]] [[SpouseLastName]])
(The String: option is included to ensure that the spaces are treated as text)
-or-
If Value1 operator Value2 then [[UserFirstName]] " " [[UserLastName]] else [[SpouseFirstName]] " " [[SpouseLastName]] endif
(The spaces between the names are enclosed in quotes, or they would be ignored)
Using functions in the True/False values allows these If functions to return the sum of a table column, or the average of the values in that column.
$If(TriggerCode,Sum([[Column1]]),Avg([[Column1]]))
-or-
If Value1 operator Value2 then Sum([[Column1]]) else Avg([[Column1]]) endif
Multiple If functions can be included in a single calculated field. The results can be used with arithmetic formulas or simple concatenation to form the final value of the calculated field.
($If(TriggerCode,TrueValue,FalseValue)+[[Value]])*$If(TriggerCode,TrueValue,FalseValue)/2
-or-
(If Value1 operator Value2 then TrueValue else FalseValue endif + [[Value]]) * If Value3 operator Value4 then TrueValue else FalseValue endif / 2
These functions take the conditional result of the first If statement and add it to the amount in the Value field, then multiply that by the conditional result of the second If statement, and divide the entire value by 2.
Multiple If functions can be chained to check one condition, then another, then another, and so on. The elseif function is used to insert a new condition when the preceding condition evaluates as false.
The following structure refers to a radio button field with three options. It systematically checks for each value, and returns a specific result when it finds the selected value.
If [[Field]] = "Option 1" then "Option 1 selected"
elseif [[Field]] = "Option 2" then "Option 2 selected"
elseif [[Field]] = "Option 3" then "Option 3 selected"
else "No option selected"
endif
The indented multiple-line structure is recommended to help organize complex conditional formulas. Also, it is best to include a final else statement in case none of the conditions evaluate to true.
Tip: When using functions within functions or using brackets to control the order of operations, be sure to close all brackets at the appropriate places. The total number of closing brackets in the entire formula must match the number of opening brackets, any misplaced brackets will cause errors.